/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package data;

import entities.ArtPhoto;
import entities.Artist;
import entities.Artwork;
import entities.Institution;
import entities.User;
import exceptions.AuthException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import reginAPI.ReginFacade;

/**
 *
 * @author bertram
 */
public class DataProvider {

    private PostGres pg;
    private static DataProvider instance;

    public DataProvider() {
        pg = PostGres.getInstance();
    }

    public static DataProvider getInstance() {
        if (instance == null) {
            instance = new DataProvider();
        }
        return instance;
    }

    public Artwork getWorkByKidID(int kidID) {
        Artwork work = null;
        try {
            Connection conn = pg.getConnection();
            String sql = "select * from kunstdeling.kunstvaerk where kunstdeling.kunstvaerk.regin_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, kidID);
            ResultSet res = stmt.executeQuery();


            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return ReginFacade.getArtwork(kidID, "");
            }
            res.next();
            int workID = res.getInt("vaerk_id");
            stmt.close();
            res.close();
            conn.close();


            return getWork(workID);


        } catch (SQLException ex) {
            System.out.println(ex.getMessage() + "getWork");
            work = new Artwork();
            work.setTitel("navn: " + ex.getMessage());
        }
        return work;
    }

    public int getNextBillede() throws SQLException {
        Connection conn = pg.getConnection();
        String sql = "SELECT nextval('kunstdeling.\"billede_billedID_seq\"'::regclass)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet res = stmt.executeQuery();
        res.next();
        int next = res.getInt(1);
        res.close();
        conn.close();
        return next;

    }

    public int getNextWork() throws SQLException {
        Connection conn = pg.getConnection();
        String sql = "SELECT nextval('kunstdeling.\"kunstvaerk_vaerkID_seq\"'::regclass)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet res = stmt.executeQuery();
        res.next();
        int next = res.getInt(1);
        res.close();
        conn.close();
        return next;

    }

    public int getNextArtist() throws SQLException {
        Connection conn = pg.getConnection();
        String sql = "SELECT nextval('kunstdeling.\"kunstner_kunstnerID_seq\"'::regclass)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet res = stmt.executeQuery();
        res.next();
        int next = res.getInt(1);
        res.close();
        conn.close();
        return next;

    }

    public int getNextArtistGroup() throws SQLException {
        Connection conn = pg.getConnection();
        String sql = "SELECT nextval('kunstdeling.\"kunstnerGruppe_kunstnergruppeID_seq\"'::regclass)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet res = stmt.executeQuery();
        res.next();
        int next = res.getInt(1);
        res.close();
        conn.close();
        return next;

    }

    public int getNextTagID() throws SQLException {
        Connection conn = pg.getConnection();
        String sql = "SELECT nextval('kunstdeling.\"tag_tagID_seq\"'::regclass)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet res = stmt.executeQuery();
        res.next();
        int next = res.getInt(1);
        res.close();
        conn.close();
        return next;
    }

    public Artist getArtist(int artistID) {
        Artist artist = new Artist(artistID);
        try {
            Connection conn = pg.getConnection();
            String sql = "select * from kunstdeling.kunstner where kunstner_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, artistID);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return artist;//Ingen grund til at fortsætte
            }
            res.next();
            artist.setArtistID(res.getInt("kunstner_id"));
            artist.setFirstName(res.getString("fornavn"));
            artist.setLastName(res.getString("efternavn"));
            conn.close();


        } catch (SQLException ex) {
            System.out.println(ex.getMessage() + "getartist");
        }
        return artist;
    }

    public Artist getArtist(String name) {
        String firstName = name;
        String lastName = "";
        if (name.contains(" ")) {
            String[] names = name.split(" ", 2);
            firstName = names[0];
            lastName = names[1];
        }

        try {
            Connection conn = pg.getConnection();
            String sql = "select * from kunstdeling.v_kunstner where navn ilike ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, " " + name);//grimt grimt hack
            
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                Artist a = new Artist();
                a.setFirstName(firstName);
                a.setLastName(lastName);
                return a;
            }
            res.next();
            Artist artist = new Artist();
            artist.setFirstName(res.getString("fornavn"));
            artist.setLastName(res.getString("efternavn"));
            artist.setArtistID(res.getInt("kunstner_id"));
            artist.setReginID(res.getInt("regin_id"));
            conn.close();
            return artist;
        } catch (SQLException ex) {
            System.out.println(ex.getMessage() + "getWork");
            return new Artist();
        }
        //return artist;
    }

    public Artwork getWork() {
        return new Artwork();
    }

    public LinkedList<String> getTags(int workID) {
        LinkedList<String> tags = new LinkedList<String>();
        try {
            Connection conn = pg.getConnection();
            String sql = "SELECT tag.tag_text FROM kunstdeling.kunstvaerk, "
                    + "kunstdeling.tag, kunstdeling.vaerk_tag WHERE "
                    + "tag.tag_id = vaerk_tag.tag_id AND "
                    + "vaerk_tag.vaerk_id = kunstvaerk.vaerk_id "
                    + "and kunstvaerk.vaerk_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, workID);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst()) {
                conn.close();
                return tags;
            }
            //res.next();
            while (res.next()) {
                tags.add(res.getString("tag_text"));
            }

        } catch (SQLException sqx) {
            System.out.println("kunne ikke hente tags: " + sqx.getMessage());
        }
        return tags;
    }

    public Artwork getWork(int workID) {
        Artwork work = new Artwork(workID);

        try {
            Connection conn = pg.getConnection();
            String sql = "select * from kunstdeling.kunstvaerk where kunstdeling.kunstvaerk.vaerk_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, workID);
            ResultSet res = stmt.executeQuery();


            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return work;//Ingen grund til at fortsætte
            }
            res.next();
            work.setWorkID(res.getInt("vaerk_id"));
            work.setTitel(res.getString("titel"));
            work.setDescription(res.getString("beskrivelse"));
            work.setFinished(res.getInt("aarstal"));
            work.setOwner(res.getString("ejer"));
            work.setType(res.getInt("type"));
            work.setTechnique(res.getString("materialer"));
            work.setReginID(res.getInt("regin_id"));
            conn.close();
            for (Artist a : getArtists(workID)) {
                work.addArtist(a);
            }
            for (ArtPhoto pht : getPhotosByWorkID(workID)) {
                work.addPhoto(pht);
            }
            for (String s : getTags(workID)) {
                work.addTag(s);
            }
        } catch (SQLException ex) {
            System.out.println(ex.getMessage() + "getWork");
            work = new Artwork();
            work.setTitel("navn: " + ex.getMessage());
        }
        return work;
    }

    public ArtPhoto getPhoto() {
        return new ArtPhoto();
    }

    public ArtPhoto getPhoto(int imageID) {
        ArtPhoto pht = new ArtPhoto(imageID);

        try {
            Connection conn = pg.getConnection();
            String sql = "select * from kunstdeling.billede where kunstdeling.billede.billed_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, imageID);
            ResultSet res = stmt.executeQuery();

            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                pht = ImageCache.getPhoto(imageID);
                return pht;//Ingen grund til at fortsætte
            }
            res.next();
            pht.setImageID(res.getInt("billed_id"));
            pht.setType(res.getString("type"));
            pht.setPublicized(res.getBoolean("public"));
            int instId = res.getInt("provided_by");
            pht.setProvidedBy(getInstitutionByID(instId).getName());
            conn.close();
        } catch (SQLException sqx) {
            System.out.println("kunne ikke loade billede via ID");
        }
        return pht;
    }

    public ArrayList<Artist> getArtists(int workID)//private?
    {
        ArrayList<Artist> artists = new ArrayList<Artist>();
        try {
            Connection conn = pg.getConnection();
            String sql = "select * from kunstdeling.kunstner"
                    + " join kunstdeling.vaerk_kunstner"
                    + " on vaerk_kunstner.kunstner_id = kunstner.kunstner_id"
                    + " where vaerk_kunstner.vaerk_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, workID);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return artists;
            }

            while (res.next()) {
                Artist a = new Artist(res.getInt("kunstner_id"));
                a.setFirstName(res.getString("fornavn"));
                a.setLastName(res.getString("efternavn"));
                a.setArtistID(res.getInt("kunstner_id"));
                artists.add(a);
            }
            conn.close();

        } catch (SQLException ex) {
            System.out.println("artist sql ups" + ex.getMessage());
        }
        return artists;
    }

    public ArrayList<ArtPhoto> getPhotosByInstID(int instID, boolean justNonAttached)//private?
    {
        ArrayList<ArtPhoto> photos = new ArrayList<ArtPhoto>();
        try {
            Connection conn = pg.getConnection();
            String sql = null;
            if(justNonAttached)
                sql = "SELECT billede.billed_id,   billede.public,   billede.vaerk_id, billede.type,   "
                    + "billede.provided_by   "
                    + "FROM   kunstdeling.billede "
                    + "where billede.provided_by = ? AND vaerk_id is null;";
            else{
                sql = "SELECT billede.billed_id,   billede.public,   billede.vaerk_id, billede.type,   "
                    + "billede.provided_by   "
                    + "FROM   kunstdeling.billede "
                    + "where billede.provided_by = ? ";}
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, instID);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return photos;
            }
            while (res.next()) {
                ArtPhoto pht = new ArtPhoto();
                pht.setImageID(res.getInt("billed_id"));
                pht.setType(res.getString("type"));
                pht.setPublicized(res.getBoolean("public"));
                pht.setProvidedBy(res.getString("provided_by"));
                pht.setWorkID(res.getInt("vaerk_id"));
                photos.add(pht);
            }
            conn.close();
        } catch (SQLException ex) {
            System.out.println("fejl: " + ex.getMessage());
        }
        return photos;
    }

    public ArrayList<ArtPhoto> getPhotosByWorkID(int workID)//private?
    {
        ArrayList<ArtPhoto> photos = new ArrayList<ArtPhoto>();
        try {
            Connection conn = pg.getConnection();
            String sql = "SELECT billede.billed_id,   billede.public,   billede.vaerk_id, billede.type,   "
                    + "billede.provided_by,   institution.institution_id,   "
                    + "institution.navn FROM   kunstdeling.billede, "
                    + "kunstdeling.institution WHERE   "
                    + "billede.provided_by = institution.institution_id AND billede.vaerk_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, workID);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return photos;
            }
            while (res.next()) {
                ArtPhoto pht = new ArtPhoto();
                pht.setImageID(res.getInt("billed_id"));
                pht.setType(res.getString("type"));
                pht.setPublicized(res.getBoolean("public"));
                pht.setProvidedBy(res.getString("navn"));
                photos.add(pht);
            }
            conn.close();
        } catch (SQLException ex) {
            System.out.println("fejl: " + ex.getMessage());
        }
        return photos;
    }

    public int getInstitutionByUser(String userName) {
        int id = 0;
        Connection conn;
        try {
            conn = pg.getConnection();
            String sql = "select \"institutionID\" FROM kunstdeling.users where user_name like ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, userName);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return id;
            }
            res.next();
            id = res.getInt("institutionID");
            conn.close();
        } catch (Exception ex) {
            System.out.println("getinstitutionbyuser" + ex.getMessage());
        }
        return id;
    }

    public int getInstitutionByName(String iName) {
        int id = 0;
        Connection conn;
        try {
            conn = pg.getConnection();
            String sql = "select institution_id FROM kunstdeling.institution where navn like ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, iName);
            ResultSet res = stmt.executeQuery();
            
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return id;
            }
            res.next();
            id = res.getInt("institution_id");
            conn.close();
        } catch (Exception ex) {
            System.out.println("getinstitutionbyname?" + ex.getMessage());
        }
        return id;
    }

    public Institution getInstitutionByID(int id) {
        Institution inst = new Institution();
        Connection conn;
        try {
            conn = pg.getConnection();
            String sql = "select institution_id, navn, reginbruger, reginpass, email FROM kunstdeling.institution where institution_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return inst;
            }
            res.next();
            inst.setName(res.getString("navn"));
            inst.setEmail(res.getString("email"));
            inst.setReginUser(res.getString("reginbruger"));
            inst.setReginPass(res.getString("reginpass"));
            inst.setId(res.getInt("institution_id"));
            conn.close();
        } catch (Exception ex) {
            System.out.println("getinstitutionbyid?" + ex.getMessage());
        }
        return inst;
    }

    public boolean savePhotos(ArrayList<ArtPhoto> photos)//burde være private? skal kun bruges af storeWork()
    {

        for (ArtPhoto pht : photos) {
            int instId = getInstitutionByName(pht.getProvidedBy());
            if (hasID("billede", "billed_id", pht.getImageID())) {
                return false;
            }
            Connection conn;
            try {
                conn = pg.getConnection();
                String sql = "INSERT INTO kunstdeling.billede(billed_id, public,  type, provided_by) VALUES (?, ?, ?, ?)";
                PreparedStatement stmt = conn.prepareStatement(sql);
                stmt.setInt(1, pht.getImageID());
                stmt.setBoolean(2, true);
                stmt.setString(3, pht.getType());
                stmt.setInt(4, instId);
                stmt.execute();
                conn.close();
            } catch (SQLException ex) {
                System.out.println("ups, kunne ikke losse billede i db: " + ex.getMessage());
            }
        }

        return false;
    }

    public boolean savePhoto(ArtPhoto pht, int vaerkID)//burde være private? skal kun bruges af storeWork()
    {
        int instId = getInstitutionByName(pht.getProvidedBy());
        Connection conn;

        try {
            String upsertSql = "";
            if (hasID("billede", "billed_id", pht.getImageID())) {
                upsertSql = "UPDATE kunstdeling.billede SET  "
                        + "  public=?,vaerk_id =?, type=?, "
                        + "provided_by=?  where billed_id = ?;";
            } else {
                upsertSql = "INSERT INTO kunstdeling.billede"
                        + "( public, vaerk_id, type, provided_by, billed_id,) "
                        + "VALUES (?, ?, ?, ?, ?)";
            }
            conn = pg.getConnection();
            PreparedStatement stmt = conn.prepareStatement(upsertSql);
            stmt.setInt(5, pht.getImageID());
            stmt.setBoolean(1, true);
            stmt.setInt(2, vaerkID);
            stmt.setString(3, pht.getType());
            stmt.setInt(4, instId);
            stmt.execute();
            conn.close();
        } catch (SQLException ex) {
            System.out.println("ups, kunne ikke losse billede i db: " + ex.getMessage());
        }

        return false;
    }

    public void saveArtist(Artist a, int workID) {
        if (hasID("kunstner", "kunstner_id", a.getArtistID())) {
        } else {
            saveArtist(a);
        }
        try {
            Connection conn = pg.getConnection();
            String sql = "SELECT * FROM kunstdeling.vaerk_kunstner WHERE vaerk_id = ? AND kunstner_id = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, workID);
            stmt.setInt(2, a.getArtistID());
            ResultSet res = stmt.executeQuery();
            String upsertSql = "";
            if (res.isBeforeFirst())//den findes allerede, og det er bare en relation, så pfft.
            {
                return;
            } else {//den findes ikke
                upsertSql = "INSERT INTO kunstdeling.vaerk_kunstner(vaerk_id, kunstner_id) "
                        + " VALUES (?, ?);";
            }
            res.close();
            stmt.close();
            PreparedStatement upsertStmt = conn.prepareStatement(upsertSql);
            upsertStmt.setInt(1, workID);
            upsertStmt.setInt(2, a.getArtistID());
            upsertStmt.execute();
            conn.close();

        } catch (Exception sqx) {
            System.out.println("saveArtist(a, w)" + sqx.getMessage());
        }

    }

    public void saveArtist(Artist a) {//må aldrig kaldes uden et upsert check inden!!!
        try {
            Connection conn = pg.getConnection();
            String sql = "INSERT INTO kunstdeling.kunstner(kunstner_id, fornavn, efternavn, regin_id) VALUES (?, ?, ?, ?);";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, a.getArtistID());
            stmt.setString(2, a.getFirstName());
            stmt.setString(3, a.getLastName());
            stmt.setInt(4, a.getReginID());
            stmt.execute();
            stmt.close();
            conn.close();

        } catch (SQLException sqx) {
            System.out.println("saveArtist(a)" + sqx.getMessage());
        }
    }

    public boolean saveWork(Artwork work) {
        boolean success = false;
        try {
            Connection conn = pg.getConnection();
            String sql = "select vaerk_id from kunstdeling.kunstvaerk where vaerk_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, work.getWorkID());
            ResultSet res = stmt.executeQuery();
            String upsertSql = "";
            if (res.isBeforeFirst())//den findes allerede
            {
                upsertSql = "UPDATE kunstdeling.kunstvaerk SET  "
                        + "titel=?, regin_id=?, "
                        + "ejer=?, beskrivelse=?, aarstal=?, type=?, materialer=? where vaerk_id = ?;";
            } else {//den findes ikke
                upsertSql = "INSERT INTO kunstdeling.kunstvaerk( titel,"
                        + "  regin_id, ejer, beskrivelse,"
                        + "aarstal, type, materialer, vaerk_id) "
                        + " VALUES (?, ?, ?, ?, ?, ?, ?, ?);";
            }
            stmt.close();
            res.close();
            PreparedStatement upsertStmt = conn.prepareStatement(upsertSql);
            upsertStmt.setInt(8, work.getWorkID());
            upsertStmt.setString(7, work.getTechnique());
            upsertStmt.setInt(6, work.getType());
            upsertStmt.setString(1, work.getTitel());
            if (work.getReginID() != 0) {
                upsertStmt.setInt(2, work.getReginID());
            } else {
                upsertStmt.setNull(2, java.sql.Types.NULL);
            }
            upsertStmt.setString(3, work.getOwner());
            upsertStmt.setString(4, work.getDescription());
            upsertStmt.setInt(5, work.getFinished());
            upsertStmt.execute();
            conn.close();
            for (ArtPhoto pht : work.getPhotos().values()) {
                savePhoto(pht, work.getWorkID());
            }
            
            for (Artist artist : work.getArtists().values()) {
                saveArtist(artist, work.getWorkID());
            }
            saveTags(work);
            success = true;

            // WorkCache.addWork(work);
        } catch (SQLException sqx) {
            System.out.println("det er savework der fejler noget" + sqx.getMessage());
        }
        return success;
    }

    public void addTag(String tag, int workID) {
        Artwork work = new Artwork(workID);
        work.addTag(tag);
        saveTags(work);
    }

    public void saveTags(Artwork work) {
        try {

            String sql = "select tag_id from kunstdeling.tag where tag_text ilike ?";
            for (String s : work.getTags()) {
                int tagID;
                Connection conn = pg.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql);
                stmt.setString(1, s);
                ResultSet res = stmt.executeQuery();
                boolean empty = !res.isBeforeFirst();
                if (!res.isBeforeFirst()) {//tagget findes ikke, vi opretter det
                    conn.close();
                    tagID = getNextTagID();
                    String insTagSql = "INSERT INTO kunstdeling.tag(tag_id, tag_text) VALUES (?, ?)  returning tag_id;";
                    Connection insConn = pg.getConnection();
                    PreparedStatement insStmt = insConn.prepareStatement(insTagSql);
                    insStmt.setInt(1, tagID);
                    insStmt.setString(2, s);
                    ResultSet re = insStmt.executeQuery();
                    insConn.close();
                    System.out.println("success?");
                } else {
                    res.next();
                    tagID = res.getInt("tag_id");
                    res.close();
                    conn.close();
                }
                Connection relConn = pg.getConnection();
                String relSql = "insert into kunstdeling.vaerk_tag(vaerk_id, tag_id) VALUES (?, ?);";
                PreparedStatement relStmt = relConn.prepareStatement(relSql);
                relStmt.setInt(1, work.getWorkID());
                relStmt.setInt(2, tagID);
                relStmt.execute();
                relStmt.close();
                relConn.close();
            }
        } catch (Exception sqx) {
            System.out.println("savetags" + sqx.getMessage());
        }
    }

    public String getToken(String userName, String password) throws AuthException {
        String token = null;
        try {
            Connection conn = pg.getConnection();
            String sql = "select user_name, user_pass from kunstdeling.users where user_name = ? and user_pass = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, userName);
            stmt.setString(2, password);
            ResultSet res = stmt.executeQuery();

            if (res.isBeforeFirst())//hvis resultsettet IKKE er tomt
            {
                conn.close();
                token = TokenCache.generateToken();
                TokenCache.addToken(userName, token);
            } else {
                throw new AuthException("invalid user/pass");
            }
            conn.close();
        } catch (SQLException sqx) {
            System.out.println("gettoken");
        }
        return token;
    }

    public boolean checkToken(String token) {

        return TokenCache.checkToken(token);
    }

    public void cachePhoto(ArrayList<ArtPhoto> photos) {
    }

    public boolean hasID(String tableName, String columnName, int id) {
        Connection conn;
        boolean hasId = false;
        try {
            conn = pg.getConnection();
            String sql = "select * from kunstdeling." + tableName + " where " + columnName + "= ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            ResultSet res = stmt.executeQuery();
            if (res.isBeforeFirst()) {

                hasId = true;
            }
            conn.close();
        } catch (Exception sqx) {
            System.out.println("hasID");
        }
        return hasId;
    }

    public ArrayList<Artwork> getWorksByTag(String tag) {
        ArrayList<Artwork> results = new ArrayList<Artwork>();
        try {
            Connection conn = pg.getConnection();
            String sql = "select kunstvaerk.vaerk_id, titel, tag_text "
                    + "from kunstdeling.kunstvaerk "
                    + "join kunstdeling.vaerk_tag "
                    + "on vaerk_tag.vaerk_id = kunstvaerk.vaerk_id "
                    + "join kunstdeling.tag on vaerk_tag.tag_id = tag.tag_id "
                    + "where tag_text ilike ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, tag);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst()) {
                conn.close();
                return results;
            }
            while (res.next()) {
                int workID = res.getInt("vaerk_id");
                Artwork w = new Artwork(workID);
                w.setTitel(res.getString("titel"));
                w.addTag(res.getString("tag_text"));
                for (ArtPhoto p : getPhotosByWorkID(workID)) {
                    w.addPhoto(p);
                }
                results.add(w);
            }
        } catch (Exception sqx) {
            System.out.println("getworksbytag" + sqx.getMessage());
        }


        return results;
    }

    public ArrayList<Artwork> getWorksByInst(int instID, int role)
    {

        ArrayList<Artwork> results = new ArrayList<Artwork>();
        try {
            Connection conn = pg.getConnection();
            String sql = "select vaerk_id, role from vaerk_institution where institution_id = instID and role = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, instID);
            stmt.setInt(2, role);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst()) {
                conn.close();
                return results;
            }
            while (res.next()) {
                int workID = res.getInt("vaerk_id");
                Artwork w = new Artwork(workID);
                w.setTitel(res.getString("titel"));
                w.addTag(res.getString("tag_text"));
                for (ArtPhoto p : getPhotosByWorkID(workID)) {
                    w.addPhoto(p);
                }
                results.add(w);
            }
        } catch (Exception sqx) {
            System.out.println("getworksbytag" + sqx.getMessage());
        }


        return results;

    }

    public ArrayList<String> getArtistsForAC(String term) {
        ArrayList<String> ret = new ArrayList<String>();
        try {
            Connection conn = pg.getConnection();
            String sql = "select kunstner_id, navn from kunstdeling.v_kunstner where navn ilike ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, "%"+term+ "%");
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return ret;
            }
            System.out.println("resultset");
            while (res.next()) {
                ret.add(res.getInt("kunstner_id") +" "+ res.getString("navn"));

            }
            conn.close();
        } catch (Exception ex) {
            System.out.println("getforautocomplete" + ex.getMessage());
        }
        return ret;
    }

    public ArrayList<String> getTypes() {
        ArrayList<String> types = new ArrayList<String>();
        try {
            Connection conn = pg.getConnection();
            String sql = "select type_id, type_tekst from kunstdeling.vaerktype;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst())//hvis resultsettet er tomt
            {
                conn.close();
                return types;
            }
            while (res.next()) {
                types.add(res.getString("type_tekst"));
            }
            stmt.close();
            conn.close();

        } catch (SQLException sqx) {
            System.out.println("gettypes" + sqx.getMessage());
        }
        return types;
    }

    public String getTypeByID(int id) {
        String type = "";
        try {
            Connection conn = pg.getConnection();
            String sql = "select type_tekst from kunstdeling.vaerktype where type_id = ?;";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            ResultSet res = stmt.executeQuery();
            if (!res.isBeforeFirst()) {
                conn.close();
                return "";
            }
            res.next();
            type = res.getString("type_tekst");
            stmt.close();
            conn.close();

        } catch (SQLException sqx) {
            System.out.println("typebyid" + sqx.getMessage());
        }
        return type;
    }

    public void addType(int id, String tekst) {
        try {
            Connection conn = pg.getConnection();
            String sql = "insert into kunstdeling.vaerktype(type_id, type_tekst) values(?,?);";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1, id);
            stmt.setString(2, tekst);
            stmt.execute();
            stmt.close();
            conn.close();
        } catch (SQLException sqx) {
            System.out.println(sqx.getMessage());
        }
    }

    public User getUser(String username, String password) {

        try {
            Connection conn = pg.getConnection();
            String sql = "select real_name, institution_id from kunstdeling.users where user_name = ? and user_pass = ?";
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, username);
            stmt.setString(2, password);
            ResultSet res = stmt.executeQuery();

            if (res.isBeforeFirst())//hvis resultsettet IKKE er tomt
            {
                res.next();
                String name = res.getString("real_name");
                int institutionID = res.getInt("institution_id");
                res.close();
                stmt.close();
                conn.close();
                Institution inst = getInstitutionByID(institutionID);
                User user = new User(name, inst);
                return user;

            }
            conn.close();
        } catch (SQLException sqx) {
            System.out.println("getUser " + sqx.getMessage());
        }
        return null;
    }
}
